package org.nyt.medicalstore.repository;

import org.nyt.medicalstore.dto.SaleStatisticsDTO;
import org.nyt.medicalstore.model.Drug;
import org.nyt.medicalstore.model.SaleRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public interface SaleRecordRepository extends JpaRepository<SaleRecord, Long> {

    // 按时间段统计销售数据
    @Query("SELECT new org.nyt.medicalstore.dto.SaleStatisticsDTO(" +
            "sr.drug.id, sr.drug.name, " +
            "CAST(SUM(sr.quantity) AS long), SUM(sr.totalAmount), " +
            ":startDate, :endDate) " +
            "FROM SaleRecord sr " +
            "WHERE sr.saleDate BETWEEN :startDate AND :endDate " +
            "GROUP BY sr.drug.id, sr.drug.name")
    List<SaleStatisticsDTO> getSalesStatistics(
            @Param("startDate") Date startDate,
            @Param("endDate") Date endDate);

    // 获取销售排行（按数量）
    @Query("SELECT new org.nyt.medicalstore.dto.SaleStatisticsDTO(" +
            "sr.drug.id, sr.drug.name, " +
            "CAST(SUM(sr.quantity) AS long), SUM(sr.totalAmount), " +
            ":startDate, :endDate) " +
            "FROM SaleRecord sr " +
            "WHERE sr.saleDate BETWEEN :startDate AND :endDate " +
            "GROUP BY sr.drug.id, sr.drug.name " +
            "ORDER BY SUM(sr.quantity) DESC")
    List<SaleStatisticsDTO> getSalesRankingByQuantity(
            @Param("startDate") Date startDate,
            @Param("endDate") Date endDate);

    // 获取销售排行（按金额）
    @Query("SELECT new org.nyt.medicalstore.dto.SaleStatisticsDTO(" +
            "sr.drug.id, sr.drug.name, " +
            "CAST(SUM(sr.quantity) AS long), SUM(sr.totalAmount), " +
            ":startDate, :endDate) " +
            "FROM SaleRecord sr " +
            "WHERE sr.saleDate BETWEEN :startDate AND :endDate " +
            "GROUP BY sr.drug.id, sr.drug.name " +
            "ORDER BY SUM(sr.totalAmount) DESC")
    List<SaleStatisticsDTO> getSalesRankingByAmount(
            @Param("startDate") Date startDate,
            @Param("endDate") Date endDate);

    // 查询用户最近购买的药品
    @Query("SELECT DISTINCT sr.drug FROM SaleRecord sr WHERE sr.customer.id = :userId ORDER BY sr.saleDate DESC")
    List<Drug> findRecentDrugsByUserId(@Param("userId") Long userId, @Param("limit") int limit);
    
    // 查询销量最高的药品
    @Query("SELECT sr.drug FROM SaleRecord sr GROUP BY sr.drug ORDER BY SUM(sr.quantity) DESC")
    List<Drug> findTopSellingDrugs(@Param("limit") int limit, @Param("offset") int offset);
}